#include "head.h"

//添加住户信息函数
int insert_resident(resident a){
    char sql[250];
    sprintf(sql,"insert into resident(name,phone,id_card,time)"
                  "values('%s','%s','%s','%s');",a.name,a.phone,a.id_card,a.time);
    int ls =sqlite3_exec(db,sql,NULL,NULL,NULL);
       if(ls!=SQLITE_OK){
           return -1;
       }
    return 0;
}
//查询住户信息
void select_resident(char * name){
    const char * sql;
    if(strcmp(name,"all")==0){
         sql = "select r.person_id as 人员编号,"
               "r.name as 姓名, "
               "r.phone as 电话, "
               "r.id_card as 身份证, "
               "h.floor_name as 楼栋号, "
               "h.house_num as 房间号, "
               "h.time as 入住日期, "
               "p.park_number as 车位 "
               "from resident r "
               "LEFT JOIN house h "
               "ON r.person_id= h.person_id "
               "LEFT JOIN park p "
               "ON r.person_id=p.person_id "
               "GROUP BY r.person_id;";
    }else{
        sql = "select r.person_id as 人员编号,"
              "r.name as 姓名, "
              "r.phone as 电话, "
              "r.id_card as 身份证, "
              "h.floor_name as 楼栋号, "
              "h.house_num as 房间号, "
              "h.time as 入住日期, "
              "p.park_number as 车位 "
              "from resident r "
              "LEFT JOIN house h "
              "ON r.person_id= h.person_id "
              "LEFT JOIN park p "
              "ON r.person_id=p.person_id "
              "where r.name=?; ";
    }
    sqlite3_stmt * stmt;
    sqlite3_prepare_v2(db,sql,-1,&stmt,NULL);
    sqlite3_bind_text(stmt,1,name,-1,SQLITE_TRANSIENT);
    //查列数
    int resident_head_count = sqlite3_column_count(stmt);
    //据算行数
    int a=0;
    while (sqlite3_step(stmt)==SQLITE_ROW){
     a++;
    }
    if(a==0){
        printf("查无此人！\n");
        return;
    }
    for (int i = 0; i < resident_head_count; i++) {
        printf("%s|\t", sqlite3_column_name(stmt, i));
    }
    printf("\n");
    while (sqlite3_step(stmt)==SQLITE_ROW){
        int id = sqlite3_column_int(stmt,0);
        char * name = sqlite3_column_text(stmt,1);
        char * phone = sqlite3_column_text(stmt,2);
        char * id_card = sqlite3_column_text(stmt,3);
        char * floor_name = sqlite3_column_text(stmt,4);
        char * house_num = sqlite3_column_text(stmt,5);
        char * time = sqlite3_column_text(stmt,6);
        char * park_number = sqlite3_column_text(stmt,7);
        printf("|%d\t|%s\t|%s\t|%s\t|%s\t|%s\t|%s\t|%s\t|\n"
               ,id,name,phone,id_card,floor_name,house_num,time,park_number);
    }
    //释放语句句柄
    sqlite3_finalize(stmt);
}
//根据住户身份证删除住户信息
 int delete_resident(char * id_card){
    sqlite3_stmt * stmt;
    char * sql1[250];
    sprintf(sql1,"select id_card from resident where id_card='%s';",id_card);
    sqlite3_prepare_v2(db,sql1,-1,&stmt,NULL);
    int a=0;
    while (sqlite3_step(stmt)==SQLITE_ROW){
        a++;
    }
    if(a==0){
        printf("没有该身份证住户，请重新输入:\n");
        sqlite3_finalize(stmt);
        return -1;
    }
    char sql[250];

    sprintf(sql,"delete from resident where id_card = '%s';",id_card);
    sqlite3_exec(db,sql,NULL,NULL,NULL);
    printf("删除成功!\n");
    sqlite3_finalize(stmt);
    return 0;
}
//用于判断更新该身份证用户是否存在
int update_resident(char *id_card){
    int a=0;
    sqlite3_stmt * stmt;
    char * sql1[250];
    sprintf(sql1,"select id_card from resident where id_card='%s';",id_card);
    sqlite3_prepare_v2(db,sql1,-1,&stmt,NULL);
    while (sqlite3_step(stmt)==SQLITE_ROW){
        a++;
    }
    if(a==0){
        printf("没有该身份证住户，请重新输入:\n");
        sqlite3_finalize(stmt);
        return -1;
    }
    sqlite3_finalize(stmt);
    return 0;
}
//用于更改信息的二次导航
void update_resident2(char *id_card){
    int a;//用于导航选择
    int b=1;//循环条件
    int c=0;//避免重复提醒
    char d[50];//存放新改的内容
    int e;//存放避免重复登记身份证
    printf("----------------请选择要更改身份证为:%s的什么内容----------------\n",id_card);
    printf("1.更改姓名 2.更改电话 3.更改身份证号 4.更改入住时间 5.退出\n");
    a= hcq(a);
    while(b){
        if(c!=0){
            printf("----------------选择继续对身份证为:%s更改的内容----------------\n",id_card);
            printf("1.更改姓名 2.更改电话 3.更改身份证号 4.更改入住时间 5.退出\n");
            a= hcq(a);
        }
        c++;
        switch (a) {
            case 1:
                printf("请输入新姓名！\n");
                scanf("%s",d);
                    e=update_resident3(id_card,d,"name");
                    if(e==-1){
                        printf("更改失败\n");
                    }else{
                        printf("更改成功！\n");
                    }
                break;
            case 2:
                printf("请输入新电话！\n");
                scanf("%s",d);
                e=update_resident3(id_card,d,"phone");
                if(e==-1){
                    printf("更改失败\n");
                }else{
                    printf("更改成功！\n");
                }
                break;
            case 3:
                printf("请输入新身份证！\n");
                scanf("%s",d);
                e=update_resident3(id_card,d,"id_card");
                if(e==-1){
                    printf("该身份证已经被登记，更改失败\n");
                }else{
                    printf("更改成功！\n");
                }
                break;
            case 4:
                printf("请输入新日期！\n");
                scanf("%s",d);
                e=update_resident3(id_card,d,"time");
                if(e==-1){
                    printf("更改失败\n");
                }else{
                    printf("更改成功！\n");
                }
                break;
            case 5:
                b=0;
                break;
            default:
                printf("输入错误请重新输入:\n");
                break;
        }
   }
}
//用于更改内容
int update_resident3(char *id_card,char * new,char * head){
    char sql[250];
    sprintf(sql,"update resident set %s='%s' where id_card='%s';",head,new,id_card);
    int ls=sqlite3_exec(db,sql,NULL,NULL,NULL);
    if (ls!=SQLITE_OK){
        return -1;
    }
    return 0;
}
void resident_main(){
    //定义结构体 resident变量a 用来存放要添加的住户信息
    resident a;
    int b;//判断登记用户信息
    int c;//用于导航选择
    int d=1;//用于退出循环体
    int e=0;//避免重复导航提示
    int f;//用于判断删除登录身份证
    int g;//用于接收判断修改目标住户是否存在的返回值
    char name[50];
    char id_card[50];//存储要操作身份证号的值
    printf("1.增加住户 2.查询住户 3. 删除住户 4.修改住户信息 5.返回\n");
    c= hcq(c);
    while(d){
        if(e!=0){
            printf("1.增加住户 2.查询住户 3. 删除住户 4.修改住户信息 5.退出\n");
            c= hcq(c);
        }
        e++;
        switch (c) {
            case 1:
                printf("请输入要添加的住户信息:姓名,电话,身份证号,入住日期\n");
                while(1){
                    scanf("%s",a.name);
                    scanf("%s",a.phone);
                    scanf("%s",a.id_card);
                    scanf("%s",a.time);
                    b=insert_resident(a);
                    if(b==-1){
                        printf("该用户已登记入住，请重新输入！\n");
                        continue;
                    } else{
                        printf("添加住户成功!\n");
                        break;
                    }
                }
                break;
            case 2:
                printf("输入 \'all\'查询所有住户 或输入 \'姓名\' 查询住户信息!\n");
                scanf("%s",name);
                select_resident(name);
                break;
            case 3:
                printf("请输入您要删除的住户身份证号:\n");
               while(1){
                   scanf("%s",id_card);
                   f=delete_resident(id_card);
                   if(f == -1){
                       continue;
                   }else{
                       break;
                   }
               }
                break;
            case 4:
                printf("请输入您要更改住户人的身份证号:");
               while(1){
                   scanf("%s",id_card);
                   g=update_resident(id_card);
                   if(g ==-1){
                       continue;
                   }else{
                       update_resident2(id_card);
                       break;
                   }
               }
                break;
            case 5:
                d=0;
                break;
            default:
                printf("输入错误请重新输入:\n");
                continue;
        }
    }
}